<?php
/*
Copyright (c) 2009, Francisco Facioni
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:
    * Redistributions of source code must retain the above copyright
      notice, this list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright
      notice, this list of conditions and the following disclaimer in the
      documentation and/or other materials provided with the distribution.
    * Neither the name of Fr6 & Co. nor the
      names of its contributors may be used to endorse or promote products
      derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY Francisco Facioni ''AS IS'' AND ANY
EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL Francisco Facioni BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/


//TODO: Put validation of parameters and some error handling
//TODO: Check if it makes sense to do this class iterable and maybe do some lazy execution stuff
/**
 * The idea of DBQuery is that you have a main table and all the extra information is left joined
 * I borrowed some ideas from appengine database model
 */
abstract class DBQuery {
	/**
	 * Main table of the query
	 */
	protected $table = null;
	
	/**
	 * Database
	 */	
	protected $database = null;
	
	//Internal variables for generating the query
	private $fields = null;
	private $filters = array();
	private $joins = array();
	private $order = array();
	private $group = array();
	private $sets = array();
	
	//Configuration variables
	
	/**
	 * This variable specifies the corresponding table to a field that should be left joined
	 * 
	 * @var array
	 */
	protected $dependencies = array();
	
	/**
	 * This variable specifies the tables that can be left joined with the main table and join fields
	 * 
	 * @var array
	 */
	protected $joinTables = array();
	
	/**
	 * Here goes the name changes of the fields that could cause a conflict
	 * 
	 * @var array
	 */
	protected $aliases = array();
	
	/**
	 * This is for aestitic renaming,  it's better to use the field method instead
	 * 
	 * @var array
	 */
	protected $field_aliases = array();
	
	/**
	 * @param string $table
	 * @param Database $database
	 * @return DBQuery
	 */
	function __construct($table,$database){
		$this->table = $table;
		$this->database = $database;
	}
	
	//TODO: Check if it's better to use MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT
	/**
	 * Generates and executes the query as a select
	 * 
	 * @param int $limit
	 * @param int $offset
	 * @return DBResult
	 */
	abstract function select($limit = null,$offset = null);
	
	/**
	 * Generates and executes the query as a count
	 * 
	 * @return DBResult
	 */
	abstract function count();
	
	/**
	 * Generates and executes the query as an insert
	 * 
	 * @return DBResult
	 */
	abstract function insert();
	
	/**
	 * Generates and executes the query as an insert
	 * 
	 * @return DBResult
	 */
	abstract function update();
	
	/**
	 * Generates and executes the query as a delete
	 * 
	 * @return DBResult
	 */
	abstract function delete();
	
	/**
	 * Generates the query as a select
	 * 
	 * @param int $limit
	 * @param int $offset
	 * @return string
	 */
	protected function selectQuery($limit = null, $offset = null){
		//Put everything together
		$query = array(
			"SELECT ".$this->sqlFields(),
			"FROM $this->table",
			$this->sqlJoins(),
			$this->sqlFilters(),
			$this->sqlGroup(),
			$this->sqlOrder(),
			$this->sqlLimit($limit,$offset),
		);
		
		return implode("\n",array_filter($query));
	}
	
	/**
	 * Generates the query as a count
	 * 
	 * @return string
	 */
	protected function countQuery(){
		$query = array(
			"SELECT count(*)",
			"FROM $this->table",
			$this->sqlJoins(),
			$this->sqlFilters(),
			$this->sqlGroup(),
		);
		return implode("\n",array_filter($query));
	}
	
	/**
	 * Generates the query as an insert
	 * 
	 * @return string
	 */
	protected function insertQuery(){
		$query = array(
			"INSERT INTO $this->table",
			$this->sqlSets(),
			$this->sqlFilters(),
		);
		return implode("\n",array_filter($query));
	}
	
	/**
	 * Generates the query as an update
	 * 
	 * @return string
	 */
	protected function updateQuery(){
		$query = array(
			"UPDATE $this->table",
			$this->sqlJoins(),
			$this->sqlSets(),
			$this->sqlFilters(),
		);
		return implode("\n",array_filter($query));
	}
	
	/**
	 * Generates the query as a delete
	 * 
	 * @return string
	 */
	protected function deleteQuery(){
		$query = array(
			"DELETE FROM $this->table",
			$this->sqlJoins(),
			$this->sqlFilters(),
		);
		
		return implode("\n",array_filter($query));
	}
	
	/**
	 * Add multiple fields
	 * 
	 * @param array $fields
	 * @return DBQuery
	 */
	function fields($fields){
		foreach($fields as $alias => $field ){
			//Hack to check if we are creating an alias of the field
			if(!is_numeric($alias)){
				$this->field_aliases[$alias] = $field;
				$field = $alias;
			}
			
			$table = $this->dependency($field);
			$this->fields[$field] = $table;
		}
		
		return $this;
	}
	
	/**
	 * Add a single field
	 * 
	 * @param mixed $field
	 * @return DBQuery
	 */
	function field($field){
		if(!is_array($field))
			$field = array($field);
		
		return $this->fields($field);
	}
	
	//TODO: Check the operators, i think it can be reduced like = with an array should be the same as in
	//TODO: Check the case of the $key
	/**
	 * Add a filter to the query
	 * 
	 * @param string $key
	 * @param mixed $value
	 * @return DBQuery
	 */
	function filter($key, $value){
		$this->filters[] = $this->sqlFilter($key,$value);
		
		return $this;
	}
	
	/**
	 * Add a group of filters with an OR connector
	 * 
	 * @param array $filters
	 * @return DBQuery
	 */
	function filterOr($filters){
		$or = array();
		
		foreach($filters as $filter)
			$or[] = call_user_func_array(array($this,"sqlFilter"),$filter);
		
		$this->filters[] = "(".implode(" OR ",$or).")";
		
		return $this;
	}
	
	/**
	 * Add a set field for insert and update
	 * 
	 * @param string $key
	 * @param mixed $value
	 * @return DBQuery
	 */
	function set($key,$value){
		$this->sets[] = "$this->table.$key = ".$this->parse($value);
		
		return $this;
	}
	
	//TODO: Use - operator to specify if we are ascening or descending (default should be ascending)
	/**
	 * Add order by fields
	 * 
	 * @param mixed $fields
	 * @return DBQuery
	 */
	function order($fields){
		if(!is_array($fields))
			$fields = array($fields);
			
		foreach($fields as $field){
			$table = $this->dependency($field);
		
			if(array_key_exists($field,$this->aliases))
				$field = $this->aliases[$field];
			
			$this->order[$field] = $table;
		}
		return $this;
	}
	
	/**
	 * Add group by fields
	 * 
	 * @param mixed $fields
	 * @return DBQuery
	 */
	function group($fields){
		if(!is_array($fields))
			$fields = array($fields);
			
		foreach($fields as $field){
			$table = $this->dependency($field);
		
			if(array_key_exists($field,$this->aliases))
				$field = $this->aliases[$field];
			
			$this->group[$field] = $table;
		}
		return $this;
	}
	
	/**
	 * Add a filter using raw SQL
	 * 
	 * @param string $sql
	 * @return DBQuery
	 */
	function filterSQL($sql){
		$filter = $this->replaceFields($sql);
		
		$args = array($filter);
		foreach(func_get_args() as $i => $arg){
			if($i == 0) continue;
			$args[] = $arg;
		}
		$filter = call_user_func_array(array($this,'replaceValues'),$args);
		
		$this->filters[] = "($filter)";
		
		return $this;
	}
	
	/**
	 * Add a field using raw SQL
	 * 
	 * @param string $sql
	 * @return DBQuery
	 */
	function fieldSQL($sql){
		$field = $this->replaceFields($sql);
		
		$this->fields[$field] = null;
		
		return $this;
	}
	
	/**
	 * Generates a raw SQL query
	 * 
	 * @param string $sql
	 * @return string
	 */
	function sql($sql){
		$sql = $this->replaceFields($sql);
		$args = array($sql);
		foreach(func_get_args() as $i => $arg){
			if($i == 0) continue;
			$args[] = $arg;
		}
		return call_user_func_array(array($this,'replaceValues'),$args);
	}
	
	/**
	 * Generates SQL code for filters
	 * 
	 * @param string $key
	 * @param mixed $value
	 * @return string
	 */
	private function sqlFilter($key,$value){
		$matches = null;
		preg_match("/^(?<key>[^\s]+)(\s+(?<operator><=|<|=|>=|>|!=|in))?$/i",$key,$matches);

		$key = $matches["key"];
		$operator = strtolower($matches["operator"]);
		
		//The default operator is =
		if(empty($operator))
			$operator = "=";

		$value = $this->parse($value);
		
		if($operator == "in"){
			if(is_array($value))
				$value = implode(",",$value);
			$value = "({$value})";
			
			$operator = strtoupper($operator);
		}
		
		if($value == "NULL"){
			$transform = array("="=>"IS","!=" => "IS NOT");
			$operator = $transform[$operator];
		}
		
		$table = $this->dependency($key);
		
		if(array_key_exists($key,$this->aliases))
			$key = $this->aliases[$key];
			
		$filter = "$table.$key $operator $value";
		
		if($operator == "!=" && $value != "NULL")
			$filter = "($filter OR $table.$key IS NULL)";
		
		return $filter;
	}
	
	/**
	 * Generates the table name
	 * 
	 * @param mixed $table
	 * @return string
	 */
	private function sqlTable($table){
		return is_array($table)?"{$table[0]}{$table[1]}":$table;
	}
	
	/**
	 * Order by SQL code generation
	 * 
	 * @return string
	 */
	private function sqlOrder(){
		if(!empty($this->order))
			return "ORDER BY ".implode(",",$this->addTableToFilters($this->order));
		
		return "";
	}
	
	/**
	 * Group by SQL code generation
	 * 
	 * @return string
	 */
	private function sqlGroup(){
		if(!empty($this->group))
			return "GROUP BY ".implode(",",$this->addTableToFilters($this->group));
		
		return "";
	}
	
	/**
	 * Create the filters SQL code
	 * 
	 * @return string
	 */
	private function sqlFilters(){
		if(!empty($this->filters))
			return "WHERE ".implode(" AND ",$this->filters);
		
		return "";
	}
	
	/**
	 * Auxiliary function for adding table name for each field
	 * 
	 * @param array $fields
	 * @return array
	 */
	private static function addTableToFilters($fields){
		$temp = array();
		foreach($fields as $field => $table)
			$temp[] = $this->sqlTable($table).".$field";
			
		return $temp;
	}
	
	/**
	 * Generates SQL code for limit
	 * 
	 * @param int $limit
	 * @param int $offset
	 * @return string
	 */
	protected function sqlLimit($limit, $offset) {
		if(!is_null($limit) && !is_null($offset))
			return "LIMIT $offset, $limit";
		else if(!is_null($limit))
			return "LIMIT $limit";
			
		return "";
	}
	
	/**
	 * Generates SQL code for fields
	 * 
	 * @return string
	 */
	private function sqlFields(){
		//Create fields SQL code
		$fields = "*";
		if(!empty($this->fields)){
			$fields = array();
			foreach($this->fields as $field => $table){
				//TODO: Instead of using table = null to mark SQL code in field use something more explicit
				//When the field is SQL code instead of a field
				if(is_null($table)){
					$fields[] = $field;
					continue;
				}
				
				$alias = null;
				
				if(array_key_exists($field,$this->field_aliases))
					$alias = $this->field_aliases[$field];
				
				if(array_key_exists($field,$this->aliases)){
					if(is_null($alias))
						$alias = $field;
					$field = $this->aliases[$field];
				}
				
				$field = $this->sqlTable($table).".$field";
				if(!is_null($alias))
					$field .= " AS $alias";
				
				$fields[] = $field;
			}
			
			$fields = implode(",",$fields);
		}
		
		return $fields;
	}
	
	/**
	 * Generates SQL code for joins
	 * 
	 * @return string
	 */
	private function sqlJoins(){
		//SQL code generation for the dependencies, every thing is left joined
		$joins = "";
		if(!empty($this->joins)){
			foreach($this->joins as $join){
				$table = key($join);
				$id = key(current($join));
				$joinFields = current(current($join));
				
				$alias = $table;
							
				if($id != "_"){
					$alias = $table.$id;
					$table = "$table AS $alias";
				}
				
				$temp = array();
				foreach($joinFields as $join){
					$temp[] = "$alias.$join[0] = $join[1]";
				}
				
				$joinFields = implode(" AND ",$temp);
				
				$joins .= <<<SQL

LEFT JOIN $table
	ON $joinFields
SQL;
			}
		}
		
		return $joins;
	}
	
	/**
	 * Generates SQL code for SET
	 * 
	 * @return string
	 */
	private function sqlSets(){
		if(!empty($this->sets))
			return "SET ".implode(",",$this->sets);
		return "";
	}
	
	/**
	 * Auxiliary function for binding fields in a SQL query
	 * 
	 * @param string $sql
	 * @return string
	 */
	private function replaceFields($sql){
		$matches = null;
        preg_match_all("/:(?<field>[^\s]+)/i",$sql,$matches);
		
        $replaces = array();
        foreach($matches["field"] as $field){
        	$table = $this->dependency($field);

        	if(array_key_exists($field,$this->aliases))
				$field = $this->aliases[$field];
			
        	$replaces[] = "$table.$field";
        }
        
        $sql = str_ireplace($matches[0],$replaces,$sql);
        
		return $sql;
	}
	
	/**
	 * Auxiliary for binding values in a SQL query
	 * 
	 * @param string $sql
	 * @return string
	 */
	private function replaceValues($sql){
        $values = array();

        foreach(func_get_args() as $i => $arg){
			//Skip the sql code
			if($i == 0) continue;
			//Named values
			if(is_array($arg)){
				foreach($arg as $key => $value){
					$values[$key] = $value;
				}
			}
			else {
				$values[$i] = $arg;
			}
        }
        
        $matches = null;
        preg_match_all("/\?(?<tag>[^\s]+)/i",$sql,$matches);

        $replaces = array();
        foreach($matches["tag"] as $tag){
        	$replaces[] = $this->parse($values[$tag]);
        }
        
        $sql = str_ireplace($matches[0],$replaces,$sql);

        return $sql;
	}
	
	/**
	 * Register the dependency tree corresponding to a field
	 * 
	 * @param string $field
	 * @return string
	 */
	private function dependency($field){
		$table = $this->dependencies[$field];
		
		//This is the case when the field belongs to the main table
		if(is_null($table))
			return $this->table;
		
		$this->join($table);
		
		return $table;
	}
	
	/**
	 * Recursive function that adds all the join statements to the query
	 * 
	 * @param string $table
	 */
	private function join($table){
		//We are using an id for identifing multiple instances of a table
		$id = "_";
		if(is_array($table)){
			$id = $table[1];
			$table = $table[0];
		}
		
		//TODO: If this is too slow, create an index by $table.$id
		//Search if the join is already defined
		foreach($this->joins as $join){
			if(key($join) == $table && $id == key(current($join))){
				return;
			}
		}
		
		//TODO: Check if it's possible to have column names that start with _. In any case think something nicer
		//Hack to check if the join definition has a second layer of ids
		//This works if there is no column name that start with _
		$tableFields = $this->joinTables[$table];
		if(array_key_exists($id,$tableFields))
			$tableFields = $tableFields[$id];
		
		$join = array();
		
		//TODO: Move SQL code generation inside ->query()
		foreach($tableFields as $field => $value){
			if(is_array($value)){
				if(is_null($value[0])){
					//this is the case of a constant
					$value = $this->parse($value[1]);
				}
				else {
					$this->join($value[0]);
					//value = table.field
					$value = "{$this->sqlTable($value[0])}.$value[1]";
				}
			}
			else{
				$value = "$this->table.$value";
			}
			
			$join[] = array($field, $value);
		}
		
		$this->joins[] = array($table => array($id => $join));
	}
	
	//TODO: Check if this function is enough to avoid SQL injection and other nasty stuff
	/**
	 * This should be the escaping of $values, to avoid SQL injection and nice SQL generation
	 * 
	 * @param mixed $value
	 * @return string
	 */
	private function parse($value){
		if(is_null($value)){
			$value = "NULL";
		}
		else if(is_bool($value)){
			$value = $value?"TRUE":"FALSE";
		}
		else if(is_numeric($value)){
			//It's fine like it is
		}
		else if(is_array($value)){
			$value = array_map(array($this,'parse'),$value);
		}
		else if(is_string($value)){
			$value = strtolower($value) == "null"?"NULL":"\"{$this->database->escape($value)}\"";
		}
		
		return $value;
	}
	
	//TODO: Remove this function when we finish the refactoring of DAs
	static function assertQueryResult($call,$params,$result){
		if(!defined("DBQUERY_DEBUG") || DBQUERY_DEBUG !== true)
			return;
		
		$oldResult = call_user_func_array($call,$params);
		
		if(!is_array($oldResult) || !is_array($result)){
			$oldResult = array($oldResult);
			$result = array($result);
		}
		
		$diff1 = array_diff_assoc_recursive($oldResult,$result);
		$diff2 = array_diff_assoc_recursive($result,$oldResult);
				
		if(!empty($diff1) || !empty($diff2)){
			echo "Old call: ";
			var_dump($oldResult);
			echo "New call: ";
			var_dump($result);
			echo "diffs: ";
			var_dump($diff1);
			var_dump($diff2);
		
			throw new Exception("The query's results don't match!!");
		}
	}
}

//TODO: Remove this auxiliary function, this is need by assertQueryResult
function array_diff_assoc_recursive($array1,$array2){
	$diff = array_diff_assoc($array1,$array2);
	
	foreach($array1 as $key => $value){
		if(!array_key_exists($key,$diff) && is_array($value)){
			$valueDiff = array_diff_assoc_recursive($value,$array2[$key]);
			if(!empty($valueDiff))
				$diff[$key] = $valueDiff;
		}
	}
	
	return $diff;
}
?>